package myta.bean;

import java.util.ArrayList;
import java.util.List;
import java.util.Map;

import javax.faces.component.html.HtmlInputText;
import javax.faces.component.html.HtmlSelectOneMenu;
import javax.faces.model.SelectItem;

import myta.base.FormDataBase;
import myta.model.DataRow;
import myta.model.DataTable;
import myta.service.Command;
import myta.service.SQLexec;

public class JadwalData extends FormDataBase{
	private HtmlInputText txtid;
	private HtmlSelectOneMenu txtidkelassiswa;
	private HtmlSelectOneMenu txtidmapel;
	private HtmlSelectOneMenu txtidguru;
	private HtmlSelectOneMenu txthari;
	
	private List<SelectItem> listKelasSiswa;
	private List<SelectItem> listMapel;
	private List<SelectItem> listGuru;
	private List<SelectItem> listHari;
	
	@Override
	public void init() throws Exception {
		formName = "jadwal";
		
		txtid = new HtmlInputText();
		txtidkelassiswa = new HtmlSelectOneMenu();
		txtidmapel = new HtmlSelectOneMenu();
		txtidguru = new HtmlSelectOneMenu();
		txthari = new HtmlSelectOneMenu();
		
		loadList();
	}
	
	private void loadList() throws Exception{
		listKelasSiswa = new ArrayList<SelectItem>();
		listMapel = new ArrayList<SelectItem>();
		listGuru = new ArrayList<SelectItem>();
		listHari = new ArrayList<SelectItem>();
		
		SelectItem s1 = new SelectItem();
		s1.setValue("Senin");
		s1.setLabel("Senin");
		
		SelectItem s2 = new SelectItem();
		s2.setValue("Selasa");
		s2.setLabel("Selasa");
		
		SelectItem s3 = new SelectItem();
		s3.setValue("Rabu");
		s3.setLabel("Rabu");
		
		SelectItem s4 = new SelectItem();
		s4.setValue("Kamis");
		s4.setLabel("Kamis");
		
		SelectItem s5 = new SelectItem();
		s5.setValue("Jumat");
		s5.setLabel("Jumat");
		
		SelectItem s6 = new SelectItem();
		s6.setValue("Sabtu");
		s6.setLabel("Sabtu");
		
		listHari.add(s1);
		listHari.add(s2);
		listHari.add(s3);
		listHari.add(s4);
		listHari.add(s5);
		listHari.add(s6);
		
		String sql = "select ks.idkelassiswa,concat(k.namakelas,' ~ ',s.thnajaran) as namakelas from kelassiswa ks inner join kelas k on ks.idkelas = k.idkelas inner join semester s on k.idsemester = s.idsemester order by ks.idkelassiswa";
		Command command = SQLexec.makeCommand(sql);
		DataTable dt = command.executeQuery();
		
		for(int i=0;i<dt.getRowCount();i++){
			DataRow dr = dt.getRow(i);
			
			SelectItem s = new SelectItem();
			s.setValue(dr.getString("idkelassiswa"));
			s.setLabel(dr.getString("namakelas"));
			
			listKelasSiswa.add(s);
		}
		
		sql = "select * from mapel order by idmapel";
		command = SQLexec.makeCommand(sql);
		dt = command.executeQuery();
		
		for(int i=0;i<dt.getRowCount();i++){
			DataRow dr = dt.getRow(i);
			
			SelectItem s = new SelectItem();
			s.setValue(dr.getString("idmapel"));
			s.setLabel(dr.getString("namamapel"));
			
			listMapel.add(s);
		}
		
		sql = "select idguru,nama from guru order by idguru";
		command = SQLexec.makeCommand(sql);
		dt = command.executeQuery();
		
		for(int i=0;i<dt.getRowCount();i++){
			DataRow dr = dt.getRow(i);
			
			SelectItem s = new SelectItem();
			s.setValue(dr.getString("idguru"));
			s.setLabel(dr.getString("nama"));
			
			listGuru.add(s);
		}
	}

	@Override
	public void generateID() throws Exception {
		String sql = "call idjadwal()";
		Command command = SQLexec.makeCommand(sql);
		DataTable datatable = command.executeQuery();
		DataRow datarow = datatable.getRow(0);
		txtid.setValue(datarow.getString("id"));
	}

	@Override
	public void getRequestData(Map<String, String> map) throws Exception {
		if(map.containsKey("idjadwal")){
			String idjadwal = map.get("idjadwal");
			
			String sql = "select * from jadwal where idjadwal = ?";
			
			Command command = SQLexec.makeCommand(sql);
			command.setObject(1, idjadwal);
			DataTable datatable = command.executeQuery();
			DataRow datarow = datatable.getRow(0);
			
			txtid.setValue(datarow.getString("idjadwal"));
			txtidkelassiswa.setValue(datarow.getString("idkelassiswa"));
			txtidmapel.setValue(datarow.getString("idmapel"));
			txtidguru.setValue(datarow.getString("idguru"));
			txthari.setValue(datarow.getString("hari"));
		}
	}

	@Override
	public boolean simpan() throws Exception {
		boolean boo = false;
		
		String sql = "INSERT INTO jadwal(idjadwal,idkelassiswa,idmapel,idguru,hari) VALUES(?,?,?,?,?);";
		Command command = SQLexec.makeCommand(sql);
		command.setObject(1, txtid.getValue().toString());
		command.setObject(2, txtidkelassiswa.getValue().toString());
		command.setObject(3, txtidmapel.getValue().toString());
		command.setObject(4, txtidguru.getValue().toString());
		command.setObject(5, txthari.getValue().toString());
		boo = command.executeUpdate();
		
		if(boo){
			return true;
		}else{
			return false;
		}		
	}

	@Override
	public boolean ubah() throws Exception {
		boolean boo = false;
		
		String sql = "UPDATE jadwal SET idkelassiswa=?,idmapel=?,idguru=?,hari=? where idjadwal = ?;";
		Command command = SQLexec.makeCommand(sql);
		command.setObject(1, txtidkelassiswa.getValue().toString());
		command.setObject(2, txtidmapel.getValue().toString());
		command.setObject(3, txtidguru.getValue().toString());
		command.setObject(4, txthari.getValue().toString());
		command.setObject(5, txtid.getValue().toString());
		boo = command.executeUpdate();
		
		if(boo){
			return true;
		}else{
			return false;
		}
	}

	public HtmlInputText getTxtid() {
		return txtid;
	}

	public void setTxtid(HtmlInputText txtid) {
		this.txtid = txtid;
	}
	
	public HtmlSelectOneMenu getTxtidkelassiswa() {
		return txtidkelassiswa;
	}

	public void setTxtidkelassiswa(HtmlSelectOneMenu txtidkelassiswa) {
		this.txtidkelassiswa = txtidkelassiswa;
	}

	public HtmlSelectOneMenu getTxtidmapel() {
		return txtidmapel;
	}

	public void setTxtidmapel(HtmlSelectOneMenu txtidmapel) {
		this.txtidmapel = txtidmapel;
	}

	public HtmlSelectOneMenu getTxtidguru() {
		return txtidguru;
	}

	public void setTxtidguru(HtmlSelectOneMenu txtidguru) {
		this.txtidguru = txtidguru;
	}

	public HtmlSelectOneMenu getTxthari() {
		return txthari;
	}

	public void setTxthari(HtmlSelectOneMenu txthari) {
		this.txthari = txthari;
	}

	public List<SelectItem> getListKelasSiswa() {
		return listKelasSiswa;
	}

	public void setListKelasSiswa(List<SelectItem> listKelasSiswa) {
		this.listKelasSiswa = listKelasSiswa;
	}

	public List<SelectItem> getListMapel() {
		return listMapel;
	}

	public void setListMapel(List<SelectItem> listMapel) {
		this.listMapel = listMapel;
	}

	public List<SelectItem> getListGuru() {
		return listGuru;
	}

	public void setListGuru(List<SelectItem> listGuru) {
		this.listGuru = listGuru;
	}

	public List<SelectItem> getListHari() {
		return listHari;
	}

	public void setListHari(List<SelectItem> listHari) {
		this.listHari = listHari;
	}
}
